%%javascript
function toggler(){
if(window.already_toggling){
// Don't add multiple buttons.
return 0
}
let btn = $('.input').append('<button>Toggle Code</button>')
.children('button');
btn.on('click', function(e){
let tgt = e.currentTarget;
$(tgt).parent().children('.inner_cell').toggle()
})
window.already_toggling = true;
}
// Since javascript cells are executed as soon as we load
// the notebook (if it's trusted), and this cell might be at the
// top of the notebook (so is executed first), we need to
// allow time for all of the other code cells to load before
// running. Let's give it 5 seconds.
setTimeout(toggler, 5000);
from IPython.display import HTML
HTML('''<script>
code_show=true;
function code_toggle() {
if (code_show){
$('div.input').hide();
} else {
$('div.input').show();
}
code_show = !code_show
}
$( document ).ready(code_toggle);
</script>
<form action="javascript:code_toggle()"><input type="submit" value="Toggle Code"></form>''')
import pandas as pd
import numpy as np
import plotly.express as px
import plotly
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"
locs = pd.read_csv('data/hospital-locations.csv')
cr = pd.read_csv('data/hcris-2010-2018.csv').drop(columns=['Unnamed: 0'])
locs = locs.rename(columns=str.lower)
cr.shape
locs.shape
(46753, 27)
(202, 10)
cr['year'].unique()
cr['provider_number'].nunique()
array([2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018])
6607
locs['provider_name'] = locs['title'].str.upper()
cr['zip'] = cr.zip.apply(lambda x: x[0:5])
df = pd.merge(cr,locs, on=['provider_name','zip'], how='inner')
df = df.sort_values(by=['provider_name','year'])
df
| report | provider_number | npi | fy_start | fy_end | date_processed | date_created | status | year | beds | ... | provider_name | type | title | address1 | city_y | state_y | latitude | longitude | primaryphone | buildingtype | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 460 | 632378.0 | 20017 | NaN | 01/01/2011 | 12/31/2011 | 09/17/2018 | 09/11/2018 | 4 | 2011 | 181.0 | ... | ALASKA REGIONAL HOSPITAL | Building | Alaska Regional Hospital | 2801 DeBarr Road | Anchorage | AK | 61.209805 | -149.829200 | (907) 276-1131 | [Hospital] |
| 461 | 560962.0 | 20017 | NaN | 01/01/2012 | 12/31/2012 | 09/29/2015 | 09/29/2015 | 2 | 2012 | 182.0 | ... | ALASKA REGIONAL HOSPITAL | Building | Alaska Regional Hospital | 2801 DeBarr Road | Anchorage | AK | 61.209805 | -149.829200 | (907) 276-1131 | [Hospital] |
| 462 | 596846.0 | 20017 | NaN | 01/01/2013 | 12/31/2013 | 05/16/2017 | 05/16/2017 | 4 | 2013 | 138.0 | ... | ALASKA REGIONAL HOSPITAL | Building | Alaska Regional Hospital | 2801 DeBarr Road | Anchorage | AK | 61.209805 | -149.829200 | (907) 276-1131 | [Hospital] |
| 463 | 631546.0 | 20017 | NaN | 01/01/2014 | 12/31/2014 | 08/22/2018 | 08/21/2018 | 4 | 2014 | 116.0 | ... | ALASKA REGIONAL HOSPITAL | Building | Alaska Regional Hospital | 2801 DeBarr Road | Anchorage | AK | 61.209805 | -149.829200 | (907) 276-1131 | [Hospital] |
| 464 | 619133.0 | 20017 | NaN | 01/01/2015 | 12/31/2015 | 02/16/2018 | 02/15/2018 | 2 | 2015 | 132.0 | ... | ALASKA REGIONAL HOSPITAL | Building | Alaska Regional Hospital | 2801 DeBarr Road | Anchorage | AK | 61.209805 | -149.829200 | (907) 276-1131 | [Hospital] |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 345 | 569020.0 | 100228 | NaN | 02/01/2013 | 01/31/2014 | 01/20/2016 | 01/05/2016 | 2 | 2013 | 224.0 | ... | WESTSIDE REGIONAL MEDICAL CENTER | Building | Westside Regional Medical Center | 8201 W Broward Blvd | Plantation | FL | 26.123543 | -80.259512 | (954) 473-6600 | [Hospital] |
| 346 | 631559.0 | 100228 | NaN | 02/01/2014 | 01/31/2015 | 08/22/2018 | 08/21/2018 | 4 | 2014 | 224.0 | ... | WESTSIDE REGIONAL MEDICAL CENTER | Building | Westside Regional Medical Center | 8201 W Broward Blvd | Plantation | FL | 26.123543 | -80.259512 | (954) 473-6600 | [Hospital] |
| 347 | 640702.0 | 100228 | NaN | 02/01/2015 | 01/31/2016 | 01/24/2019 | 01/15/2019 | 4 | 2015 | 224.0 | ... | WESTSIDE REGIONAL MEDICAL CENTER | Building | Westside Regional Medical Center | 8201 W Broward Blvd | Plantation | FL | 26.123543 | -80.259512 | (954) 473-6600 | [Hospital] |
| 348 | 635568.0 | 100228 | NaN | 02/01/2016 | 01/31/2017 | 11/14/2018 | 11/07/2018 | 1 | 2016 | 224.0 | ... | WESTSIDE REGIONAL MEDICAL CENTER | Building | Westside Regional Medical Center | 8201 W Broward Blvd | Plantation | FL | 26.123543 | -80.259512 | (954) 473-6600 | [Hospital] |
| 349 | 629687.0 | 100228 | NaN | 02/01/2017 | 01/31/2018 | 07/19/2018 | 07/18/2018 | 1 | 2017 | 224.0 | ... | WESTSIDE REGIONAL MEDICAL CENTER | Building | Westside Regional Medical Center | 8201 W Broward Blvd | Plantation | FL | 26.123543 | -80.259512 | (954) 473-6600 | [Hospital] |
518 rows × 36 columns
df_recent = df.groupby(by='provider_number').agg('last').reset_index()
df_recent.head()
| provider_number | report | npi | fy_start | fy_end | date_processed | date_created | status | year | beds | ... | provider_name | type | title | address1 | city_y | state_y | latitude | longitude | primaryphone | buildingtype | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 20017 | 631694.0 | NaN | 01/01/2017 | 12/31/2017 | 08/27/2018 | 08/27/2018 | 5 | 2017 | 164.0 | ... | ALASKA REGIONAL HOSPITAL | Building | Alaska Regional Hospital | 2801 DeBarr Road | Anchorage | AK | 61.209805 | -149.829200 | (907) 276-1131 | [Hospital] |
| 1 | 50022 | 633531.0 | NaN | 05/01/2017 | 04/30/2018 | 10/15/2018 | 10/09/2018 | 1 | 2017 | 456.0 | ... | RIVERSIDE COMMUNITY HOSPITAL | Building | Riverside Community Hospital | 4445 Magnolia Ave | Riverside | CA | 33.976668 | -117.381043 | (951) 788-3000 | [Hospital] |
| 2 | 50125 | 628880.0 | NaN | 01/01/2017 | 12/31/2017 | 07/19/2018 | 06/27/2018 | 1 | 2017 | 258.0 | ... | REGIONAL MEDICAL CENTER OF SAN JOSE | Building | Regional Medical Center of San Jose | 225 North Jackson Ave | San Jose | CA | 37.362568 | -121.849745 | (408) 259-5000 | [Hospital] |
| 3 | 50380 | 629921.0 | NaN | 02/01/2017 | 01/31/2018 | 07/24/2018 | 07/23/2018 | 1 | 2017 | 300.0 | ... | GOOD SAMARITAN HOSPITAL | Building | Good Samaritan Hospital | 2425 Samaritan Dr | San Jose | CA | 37.252071 | -121.946331 | (408) 559-2011 | [Hospital] |
| 4 | 60032 | 629818.0 | NaN | 01/01/2017 | 12/31/2017 | 07/20/2018 | 06/30/2018 | 1 | 2017 | 231.0 | ... | ROSE MEDICAL CENTER | Building | Rose Medical Center | 4567 E. 9th Ave | Denver | CO | 39.732308 | -104.933961 | (303) 320-2121 | [ER, Hospital] |
5 rows × 36 columns
fig = px.scatter_mapbox(df_recent[df_recent.state_y == 'FL'], zoom=6,
lat='latitude', lon='longitude', mapbox_style="carto-positron",
color='tot_operating_exp', size='beds', opacity=.6,
hover_name='title',hover_data=['buildingtype', 'address1','city_y','state_y'],
color_discrete_sequence=plotly.colors.qualitative.Prism_r,
)
fig = fig.update_layout(mapbox_style="carto-positron")
fig = fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
fig.show('notebook')
df.groupby('title').get_group('Alaska Regional Hospital').mean()
report 6.160721e+05 provider_number 2.001700e+04 npi NaN status 3.714286e+00 year 2.014000e+03 beds 1.538571e+02 tot_charges 7.495390e+08 tot_discounts 5.172936e+08 tot_operating_exp 1.702572e+08 ip_charges 5.105930e+07 icu_charges 5.098778e+07 ancillary_charges 3.902280e+08 tot_discharges 5.675714e+03 mcare_discharges 1.794714e+03 mcaid_discharges 8.987143e+02 tot_mcare_payment 3.410666e+07 secondary_mcare_payment 3.556300e+04 zip 1.421557e+34 latitude 6.120981e+01 longitude -1.498292e+02 dtype: float64
px.line(df.groupby(['title','year'])['tot_operating_exp'].median().reset_index(),
x='year', y='tot_operating_exp', color='title')